/*
 * @(#)DmsEmailAuditTrail.java
 *
 * Copyright (c) 2003 DCIVision Ltd
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of DCIVision
 * Ltd ("Confidential Information").  You shall not disclose such Confidential
 * Information and shall use it only in accordance with the terms of the license
 * agreement you entered into with DCIVision Ltd.
 */
package com.dcivision.dms.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

import com.dcivision.dms.bean.DmsEmailAuditTrail;
import com.dcivision.framework.ApplicationException;
import com.dcivision.framework.DataSourceFactory;
import com.dcivision.framework.ErrorConstant;
import com.dcivision.framework.GlobalConstant;
import com.dcivision.framework.SessionContainer;
import com.dcivision.framework.SystemParameterConstant;
import com.dcivision.framework.SystemParameterFactory;
import com.dcivision.framework.TextUtility;
import com.dcivision.framework.UserInfoFactory;
import com.dcivision.framework.Utility;
import com.dcivision.framework.bean.AbstractBaseObject;
import com.dcivision.framework.dao.AbstractDAObject;
import com.dcivision.framework.web.AbstractSearchForm;

/**
 DmsEmailAuditTrailDAObject.java
 
 This class is the data access bean for table "DMS_EMAIL_AUDIT_TRAIL".
 
 @author      barbin tao
 @company     DCIVision Limited
 @creation date   12/09/2006
 @version     $Revision: 1.7 $
 */

public class DmsEmailAuditTrailDAObject extends AbstractDAObject {
  
  public static final String REVISION = "$Revision: 1.7 $";
  
  public static final String TABLE_NAME = "DMS_EMAIL_AUDIT_TRAIL";
  
  public DmsEmailAuditTrailDAObject(SessionContainer sessionContainer, Connection dbConn) {
    super(sessionContainer, dbConn);
  }
  
  protected void initDBSetting() {
    this.baseTableName = TABLE_NAME;
    this.vecDBColumn.add("ID");
    this.vecDBColumn.add("EMAIL_TYPE");
    this.vecDBColumn.add("OPERATION");
    this.vecDBColumn.add("USERID");
    this.vecDBColumn.add("EMAIL_SUBJECT");
    this.vecDBColumn.add("EMAIL_FROM");
    this.vecDBColumn.add("EMAIL_CC");
    this.vecDBColumn.add("EMAIL_BCC");
    this.vecDBColumn.add("EMAIL_TO");
    this.vecDBColumn.add("SENT_DATE");
    this.vecDBColumn.add("ATTCH_NAME");
    this.vecDBColumn.add("ATTCH_SOURCE");
  }
  
  protected synchronized AbstractBaseObject getByID(Integer id) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.EMAIL_TYPE, A.OPERATION, A.USERID, A.EMAIL_SUBJECT, A.EMAIL_FROM, A.EMAIL_CC, A.EMAIL_BCC, A.EMAIL_TO, A.SENT_DATE, A.ATTCH_NAME, A.ATTCH_SOURCE ");
        sqlStat.append("FROM   DMS_EMAIL_AUDIT_TRAIL A ");
        sqlStat.append("WHERE  A.ID = ? AND A.RECORD_STATUS = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, id);
        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();
        if (rs.next()) {
          DmsEmailAuditTrail tmpDmsEmailAuditTrail = new DmsEmailAuditTrail();
          tmpDmsEmailAuditTrail.setID(getResultSetInteger(rs, "ID"));
          tmpDmsEmailAuditTrail.setEmailType(getResultSetString(rs, "EMAIL_TYPE"));
          tmpDmsEmailAuditTrail.setOperation(getResultSetString(rs, "OPERATION"));
          tmpDmsEmailAuditTrail.setUserid(getResultSetInteger(rs, "USERID"));
          tmpDmsEmailAuditTrail.setEmailSubject(getResultSetString(rs, "EMAIL_SUBJECT"));
          tmpDmsEmailAuditTrail.setEmailFrom(getResultSetString(rs, "EMAIL_FROM"));
          tmpDmsEmailAuditTrail.setEmailCc(getResultSetString(rs, "EMAIL_CC"));
          tmpDmsEmailAuditTrail.setEmailBcc(getResultSetString(rs, "EMAIL_BCC"));
          tmpDmsEmailAuditTrail.setEmailTo(getResultSetString(rs, "EMAIL_TO"));
          tmpDmsEmailAuditTrail.setSentDate(getResultSetTimestamp(rs, "SENT_DATE").toString());
          tmpDmsEmailAuditTrail.setAttchName(getResultSetString(rs, "ATTCH_NAME"));
          tmpDmsEmailAuditTrail.setAttchSource(getResultSetString(rs, "ATTCH_SOURCE"));
          tmpDmsEmailAuditTrail.setCreatorName(UserInfoFactory.getUserFullName(tmpDmsEmailAuditTrail.getCreatorID()));
          tmpDmsEmailAuditTrail.setUpdaterName(UserInfoFactory.getUserFullName(tmpDmsEmailAuditTrail.getUpdaterID()));
          return(tmpDmsEmailAuditTrail);
        } else {
          throw new ApplicationException(ErrorConstant.DB_RECORD_NOT_FOUND_ERROR);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  /******************************
   * main function: get list result for generate pdf
   * @param emailAuditTrail
   * @return
   * @throws ApplicationException
   */
  public synchronized List getPDFDefaultList(DmsEmailAuditTrail emailAuditTrail) throws ApplicationException{
    PreparedStatement preStat = null;
    ResultSet rs = null;
    ResultSet rsCnt = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();
    
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.EMAIL_TYPE, A.OPERATION, A.USER_ID, A.EMAIL_SUBJECT, A.EMAIL_FROM, A.EMAIL_CC, A.EMAIL_BCC, A.EMAIL_TO, A.SENT_DATE, A.ATTCH_NAME, A.ATTCH_SOURCE,A.ATTCH_COUNT ");
        sqlStat.append("FROM   DMS_EMAIL_AUDIT_TRAIL A ");
        sqlStat.append("WHERE  A.ATTCH_SOURCE = 'DMS' ");
        
        // Form the WHERE clause for filtering.
        if (!Utility.isEmpty(emailAuditTrail.getOperation())){
          sqlStat.append("AND  " + "A.OPERATION" + " " + emailAuditTrail.getOperationSearchType() + " ? ");
        }
        if(!Utility.isEmpty(emailAuditTrail.getUserid())){
          sqlStat.append("AND " + "A.USER_ID" + " " + emailAuditTrail.getUserSearchType() + " ? ");
        }
        if(!Utility.isEmpty(emailAuditTrail.getEmailTo())){
          sqlStat.append("AND " + "A.EMAIL_TO" + " " + emailAuditTrail.getToSearchType() + " ? ");
        }
        if(!Utility.isEmpty(emailAuditTrail.getEmailFrom())){
          sqlStat.append("AND " + "A.EMAIL_FROM" + " " + emailAuditTrail.getFromSearchType() + " ? ");
        }
        if(!Utility.isEmpty(emailAuditTrail.getEmailCc())){
          sqlStat.append("AND " + "A.EMAIL_CC" + " " + emailAuditTrail.getCcSearchType() + " ? ");
        }
        if(!Utility.isEmpty(emailAuditTrail.getEmailBcc())){
          sqlStat.append("AND " + "A.EMAIL_BCC" + " " + emailAuditTrail.getBccSearchType() + " ? ");
        }
        if(!Utility.isEmpty(emailAuditTrail.getEmailSubject())){
          sqlStat.append("AND " + "A.EMAIL_SUBJECT" + " " + emailAuditTrail.getSubjectSearchType() + " ? ");
        }
        if(!Utility.isEmpty(emailAuditTrail.getSentDate())&& emailAuditTrail.getSentDateValue().equals("specificDate")){
          if(Utility.isEmpty(emailAuditTrail.getSecondDate())){
            if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
              sqlStat.append("AND " + "A.SENT_DATE" + " " + emailAuditTrail.getSentDateSearchType() + " TO_DATE('" + TextUtility.formatDate(TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getSentDate()),"yyyy-MM-dd HH:mm:ss") + "','YYYY-MM-DD HH24:MI:SS') ");
            } else {
              sqlStat.append("AND " + "A.SENT_DATE" + " " + emailAuditTrail.getSentDateSearchType() + " '" + TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getSentDate()) + "' ");
            }
          }
          if(!Utility.isEmpty(emailAuditTrail.getSecondDate())){
            if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
              sqlStat.append("AND " + "A.SENT_DATE" + " " + emailAuditTrail.getSentDateSearchType() + " TO_DATE('" + TextUtility.formatDate(TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getSecondDate()),"yyyy-MM-dd HH:mm:ss") + "' ,'YYYY-MM-DD HH24:MI:SS') ");
            } else {
              sqlStat.append("AND " + "A.SENT_DATE" + " " + emailAuditTrail.getSentDateSearchType() + " '" + TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getSecondDate()) + "' ");
            }
          }
        }else if(!Utility.isEmpty(emailAuditTrail.getSentDateValue())&&!Utility.isEmpty(emailAuditTrail.getFromTime())){      	
          if(!Utility.isEmpty(emailAuditTrail.getToTime())){
            if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
              sqlStat.append("AND " + "A.SENT_DATE" + " " + ">" + " TO_DATE('" + TextUtility.formatDate(TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getFromTime()),"yyyy-MM-dd HH:mm:ss") + "' ,'YYYY-MM-DD HH24:MI:SS') ");
            } else { 
              sqlStat.append("AND " + "A.SENT_DATE" + " " + ">" + " '" + TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getFromTime()) + "' ");
            }
            if(true){
              if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
                sqlStat.append( "AND "+ "A.SENT_DATE" + " <" +" TO_DATE('" + TextUtility.formatDate(TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getToTime()),"yyyy-MM-dd HH:mm:ss") + "' ,'YYYY-MM-DD HH24:MI:SS') ");
              } else {
                sqlStat.append( "AND "+ "A.SENT_DATE" + " <" +" '" + TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getToTime()) + "' ");
              }
            }
          }
          if(Utility.isEmpty(emailAuditTrail.getToTime())){
            if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
              sqlStat.append("AND " + "A.SENT_DATE" + " " + "<" + " TO_DATE('" + TextUtility.formatDate(TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getFromTime()),"yyyy-MM-dd HH:mm:ss") + "' ,'YYYY-MM-DD HH24:MI:SS') ");
            } else {
              sqlStat.append("AND " + "A.SENT_DATE" + " " + "<" + " '" + TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getFromTime()) + "' ");
            }
          }
          if(Utility.isEmpty(emailAuditTrail.getSecondDate())&&!Utility.isEmpty(emailAuditTrail.getSecFromTime())){
            
            if(!Utility.isEmpty(emailAuditTrail.getSecToTime())){
              if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
                sqlStat.append("OR " + "(A.SENT_DATE" + " " + ">" + " TO_DATE('" + TextUtility.formatDate(TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getSecFromTime()),"yyyy-MM-dd HH:mm:ss") + "' ,'YYYY-MM-DD HH24:MI:SS') ");
              } else {
                sqlStat.append("OR " + "(A.SENT_DATE" + " " + ">" + " '" + TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getSecFromTime()) + "' ");
              }
              if(true){
                if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
                  sqlStat.append("AND " + "A.SENT_DATE" + " " + "<" + " TO_DATE('" + TextUtility.formatDate(TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getSecToTime()),"yyyy-MM-dd HH:mm:ss") + "' ,'YYYY-MM-DD HH24:MI:SS') ");
                } else {
                  sqlStat.append("AND " + "A.SENT_DATE" + " " + "<" + " '" + TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getSecToTime()) + "' ");
                }
              }
              sqlStat.append(")"+" ");
            }
            if(Utility.isEmpty(emailAuditTrail.getSecToTime())){
              if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
                sqlStat.append("OR " + "A.SENT_DATE" + " " + "<" + " TO_DATE('" + TextUtility.formatDate(TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getSecFromTime()),"yyyy-MM-dd HH:mm:ss") + "' ,'YYYY-MM-DD HH24:MI:SS') ");
              } else {
                sqlStat.append("OR " + "A.SENT_DATE" + " " + "<" + " '" + TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getSecFromTime()) + "' ");
              }	
            }
          }
          
        }
        if(!Utility.isEmpty(emailAuditTrail.getAttchName())){
          sqlStat.append("AND " + "A.ATTCH_NAME" + " " + emailAuditTrail.getAttchNameSearchType() + " ? ");
        }
        if(!Utility.isEmpty(emailAuditTrail.getEmailType())){
          sqlStat.append("AND " + "A.EMAIL_TYPE" + " " + emailAuditTrail.getTypeSearchType() + " ? ");
        }
        
        //format the sql for any 'LIKE' statement contained
        sqlStat = this.getFormattedSQL(sqlStat.toString());
        
        //Form the ORDER clause for sorting.
        if (!Utility.isEmpty(emailAuditTrail.getSortAttribute())) {
          String sortAttribute = emailAuditTrail.getSortAttribute();
          if (sortAttribute.indexOf(".") < 0) {
            sortAttribute = "A." + sortAttribute;
          }
          sqlStat.append("ORDER BY " + sortAttribute + " " + emailAuditTrail.getSortOrder());
        }
        
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        
        int i=0;
        //this.setPrepareStatement(preStatCnt, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        if (!Utility.isEmpty(emailAuditTrail.getOperation())){
          i++;
          this.setPrepareStatement(preStat, i, emailAuditTrail.getOperation());
        }
        if(!Utility.isEmpty(emailAuditTrail.getUserid())){
          i++;
          if(emailAuditTrail.getUserSearchType().equals("LIKE")){
            this.setPrepareStatement(preStat, i, "%"+emailAuditTrail.getUserid()+"%");
          }else{
            this.setPrepareStatement(preStat, i, emailAuditTrail.getUserid());
          }
        }
        if(!Utility.isEmpty(emailAuditTrail.getEmailTo())){
          i++;
          if(emailAuditTrail.getToSearchType().equals("LIKE")){
            this.setPrepareStatement(preStat, i, "%"+emailAuditTrail.getEmailTo()+"%");
          }else{
            this.setPrepareStatement(preStat, i, emailAuditTrail.getEmailTo());
          }
        }
        if(!Utility.isEmpty(emailAuditTrail.getEmailFrom())){
          i++;
          if(emailAuditTrail.getFromSearchType().equals("LIKE")){
            this.setPrepareStatement(preStat, i, "%"+emailAuditTrail.getEmailFrom()+"%");
          }else{
            this.setPrepareStatement(preStat, i, emailAuditTrail.getEmailFrom());
          }
        }
        if(!Utility.isEmpty(emailAuditTrail.getEmailCc())){
          i++;
          if(emailAuditTrail.getCcSearchType().equals("LIKE")){
            this.setPrepareStatement(preStat, i, "%"+emailAuditTrail.getEmailCc()+"%");
          }else{
            this.setPrepareStatement(preStat, i, emailAuditTrail.getEmailCc());
          }
        }
        if(!Utility.isEmpty(emailAuditTrail.getEmailBcc())){
          i++;
          if(emailAuditTrail.getBccSearchType().equals("LIKE")){
            this.setPrepareStatement(preStat, i, "%"+emailAuditTrail.getEmailBcc()+"%");
          }else{
            this.setPrepareStatement(preStat, i, emailAuditTrail.getEmailBcc());
          }	
        }
        if(!Utility.isEmpty(emailAuditTrail.getEmailSubject())){
          i++;
          if(emailAuditTrail.getSubjectSearchType().equals("LIKE")){
            this.setPrepareStatement(preStat, i, "%"+emailAuditTrail.getEmailSubject()+"%");
          }else{
            this.setPrepareStatement(preStat, i, emailAuditTrail.getEmailSubject());	
          }
          
        }
        /*
        if(!Utility.isEmpty(emailAuditTrail.getSentDate())&& emailAuditTrail.getSentDateValue().equals("specificDate")){
          if(Utility.isEmpty(emailAuditTrail.getSecondDate())){
            i++;
            this.setPrepareStatement(preStat, i, emailAuditTrail.getSentDate());
          }
          if(!Utility.isEmpty(emailAuditTrail.getSecondDate())){
            i++;
            this.setPrepareStatement(preStat, i, emailAuditTrail.getSecondDate());
          }
        }else if(!Utility.isEmpty(emailAuditTrail.getSentDateValue())&&!Utility.isEmpty(emailAuditTrail.getFromTime())){      	
          if(!Utility.isEmpty(emailAuditTrail.getToTime())){
            i++;
            this.setPrepareStatement(preStat, i, emailAuditTrail.getFromTime());
            if(true){
              i++;
              this.setPrepareStatement(preStat, i, emailAuditTrail.getToTime());
            }
          }
          if(Utility.isEmpty(emailAuditTrail.getToTime())){
            i++;
            this.setPrepareStatement(preStat, i, emailAuditTrail.getFromTime());
          }
          if(Utility.isEmpty(emailAuditTrail.getSecondDate())&&!Utility.isEmpty(emailAuditTrail.getSecFromTime())){
            
            if(!Utility.isEmpty(emailAuditTrail.getSecToTime())){
              i++;
              this.setPrepareStatement(preStat, i, emailAuditTrail.getSecFromTime());
              if(true){
                i++;
                this.setPrepareStatement(preStat, i, emailAuditTrail.getSecToTime());
              }
            }
            if(Utility.isEmpty(emailAuditTrail.getSecToTime())){
              i++;
              this.setPrepareStatement(preStat, i, emailAuditTrail.getSecFromTime());
            }
          }
          
        }
        */
        
        if(!Utility.isEmpty(emailAuditTrail.getAttchName())){
          i++;
          if(emailAuditTrail.getAttchNameSearchType().equals("LIKE")){
            this.setPrepareStatement(preStat, i, "%"+emailAuditTrail.getAttchName()+"%");
          }else{
            this.setPrepareStatement(preStat, i, emailAuditTrail.getAttchName());	
          }
        }
        if(!Utility.isEmpty(emailAuditTrail.getEmailType())){
          i++;
          this.setPrepareStatement(preStat, i, emailAuditTrail.getEmailType());
        }
        rs = preStat.executeQuery();
        
        while (rs.next()) {
          DmsEmailAuditTrail tmpDmsEmailAuditTrail = new DmsEmailAuditTrail();
          tmpDmsEmailAuditTrail.setID(getResultSetInteger(rs, "ID"));
          tmpDmsEmailAuditTrail.setEmailType(getResultSetString(rs, "EMAIL_TYPE"));
          tmpDmsEmailAuditTrail.setOperation(getResultSetString(rs, "OPERATION"));
          tmpDmsEmailAuditTrail.setUserid(getResultSetInteger(rs, "USER_ID"));
          tmpDmsEmailAuditTrail.setEmailSubject(getResultSetString(rs, "EMAIL_SUBJECT"));
          tmpDmsEmailAuditTrail.setEmailFrom(getResultSetString(rs, "EMAIL_FROM"));
          tmpDmsEmailAuditTrail.setEmailCc(getResultSetString(rs, "EMAIL_CC"));
          tmpDmsEmailAuditTrail.setEmailBcc(getResultSetString(rs, "EMAIL_BCC"));
          tmpDmsEmailAuditTrail.setEmailTo(getResultSetString(rs, "EMAIL_TO"));
          tmpDmsEmailAuditTrail.setSentDate(getResultSetString(rs, "SENT_DATE"));
          tmpDmsEmailAuditTrail.setAttchName(getResultSetString(rs, "ATTCH_NAME"));
          tmpDmsEmailAuditTrail.setAttchSource(getResultSetString(rs, "ATTCH_SOURCE"));
          tmpDmsEmailAuditTrail.setUserName(UserInfoFactory.getUserFullName(tmpDmsEmailAuditTrail.getUserid()));
          tmpDmsEmailAuditTrail.setAttchCount(getResultSetInteger(rs,"ATTCH_COUNT"));
          result.add(tmpDmsEmailAuditTrail);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  /*************
   * main function: the method get the result list for search
   * @param emailAuditTrail
   * @return
   * @throws ApplicationException
   */
  public synchronized List getSearchList(DmsEmailAuditTrail emailAuditTrail) throws ApplicationException {
    PreparedStatement preStat = null;
    PreparedStatement preStatCnt = null;
    ResultSet rs = null;
    ResultSet rsCnt = null;
    StringBuffer sqlStat = new StringBuffer();
    StringBuffer sqlStatCnt = new StringBuffer();
    List result = new ArrayList();
    int totalNumOfRecord = 0;
    int rowLoopCnt = 0;
    int startOffset = TextUtility.parseInteger(emailAuditTrail.getCurStartRowNo());
    int pageSize = TextUtility.parseInteger(SystemParameterFactory.getSystemParameter(SystemParameterConstant.PAGE_OFF_SET));
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.EMAIL_TYPE, A.OPERATION, A.USER_ID, A.EMAIL_SUBJECT, A.EMAIL_FROM, A.EMAIL_CC, A.EMAIL_BCC, A.EMAIL_TO, A.SENT_DATE, A.ATTCH_NAME, A.ATTCH_SOURCE,A.ATTCH_COUNT ");
        sqlStat.append("FROM   DMS_EMAIL_AUDIT_TRAIL A ");
        sqlStat.append("WHERE  A.ATTCH_SOURCE = 'DMS' ");
        
        // Form the WHERE clause for filtering.
        if (!Utility.isEmpty(emailAuditTrail.getOperation())){
          sqlStat.append("AND  " + "A.OPERATION" + " " + emailAuditTrail.getOperationSearchType() + " ? ");
        }
        if(!Utility.isEmpty(emailAuditTrail.getUserid())){
          sqlStat.append("AND " + "A.USER_ID" + " " + emailAuditTrail.getUserSearchType() + " ? ");
        }
        if(!Utility.isEmpty(emailAuditTrail.getEmailTo())){
          sqlStat.append("AND " + "A.EMAIL_TO" + " " + emailAuditTrail.getToSearchType() + " ? ");
        }
        if(!Utility.isEmpty(emailAuditTrail.getEmailFrom())){
          sqlStat.append("AND " + "A.EMAIL_FROM" + " " + emailAuditTrail.getFromSearchType() + " ? ");
        }
        if(!Utility.isEmpty(emailAuditTrail.getEmailCc())){
          sqlStat.append("AND " + "A.EMAIL_CC" + " " + emailAuditTrail.getCcSearchType() + " ? ");
        }
        if(!Utility.isEmpty(emailAuditTrail.getEmailBcc())){
          sqlStat.append("AND " + "A.EMAIL_BCC" + " " + emailAuditTrail.getBccSearchType() + " ? ");
        }
        if(!Utility.isEmpty(emailAuditTrail.getEmailSubject())){
          sqlStat.append("AND " + "A.EMAIL_SUBJECT" + " " + emailAuditTrail.getSubjectSearchType() + " ? ");
        }
        if(!Utility.isEmpty(emailAuditTrail.getSentDate())&& emailAuditTrail.getSentDateValue().equals("specificDate")){
          if(!Utility.isEmpty(emailAuditTrail.getSentDate())){
            if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
              sqlStat.append("AND " + "A.SENT_DATE" + " " + emailAuditTrail.getSentDateSearchType() + " TO_DATE('" + TextUtility.formatDate(TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getSentDate()),"yyyy-MM-dd HH:mm:ss") + "','YYYY-MM-DD HH24:MI:SS') ");
            } else {
              sqlStat.append("AND " + "A.SENT_DATE" + " " + emailAuditTrail.getSentDateSearchType() + " '" + TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getSentDate()) + "' ");
            }
          }
          if(!Utility.isEmpty(emailAuditTrail.getSecondDate())){
            if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
              sqlStat.append("AND " + "A.SENT_DATE" + " " + emailAuditTrail.getSecondDateSearchType() + " TO_DATE('" + TextUtility.formatDate(TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getSecondDate()),"yyyy-MM-dd HH:mm:ss") + "','YYYY-MM-DD HH24:MI:SS') ");
            } else {
              sqlStat.append("AND " + "A.SENT_DATE" + " " + emailAuditTrail.getSecondDateSearchType() + " '" + TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getSecondDate()) + "' ");
            }
          }
        }else if(!Utility.isEmpty(emailAuditTrail.getSentDateValue())&&!Utility.isEmpty(emailAuditTrail.getFromTime())){      	
          if(!Utility.isEmpty(emailAuditTrail.getToTime())){
            if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
              sqlStat.append("AND " + "A.SENT_DATE" + " " + "> TO_DATE('" + TextUtility.formatDate(TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getFromTime()),"yyyy-MM-dd HH:mm:ss") + "','YYYY-MM-DD HH24:MI:SS') ");
            } else {
              sqlStat.append("AND " + "A.SENT_DATE" + " " + ">" + " '" + TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getFromTime()) + "' "); 
            }
            if(true){
              if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
                sqlStat.append("AND " + "A.SENT_DATE" + " < TO_DATE('" + TextUtility.formatDate(TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getToTime()),"yyyy-MM-dd HH:mm:ss") + "','YYYY-MM-DD HH24:MI:SS') ");
              } else {
                sqlStat.append( "AND "+ "A.SENT_DATE" + " <" +" '" + TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getToTime()) + "' ");
              }
            }
          }
          if(Utility.isEmpty(emailAuditTrail.getToTime())){
            if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
              sqlStat.append("AND " + "A.SENT_DATE" + " " + "< TO_DATE('" + TextUtility.formatDate(TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getFromTime()),"yyyy-MM-dd HH:mm:ss") + "','YYYY-MM-DD HH24:MI:SS') ");
            } else {
              sqlStat.append("AND " + "A.SENT_DATE" + " " + "<" + " '" + TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getFromTime()) + "' ");
            }
          }
          if(Utility.isEmpty(emailAuditTrail.getSecondDate())&&!Utility.isEmpty(emailAuditTrail.getSecFromTime())){
            
            if(!Utility.isEmpty(emailAuditTrail.getSecToTime())){
              if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
                sqlStat.append("OR " + "(A.SENT_DATE" + " " + ">" + " TO_DATE('" + TextUtility.formatDate(TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getSecFromTime()),"yyyy-MM-dd HH:mm:ss") + "','YYYY-MM-DD HH24:MI:SS') ");
              } else {
                sqlStat.append("OR " + "(A.SENT_DATE" + " " + ">" + " '" + TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getSecFromTime()) + "' ");
              }
              if(true){
                if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
                  sqlStat.append("AND " + "A.SENT_DATE" + " " + "<" + " TO_DATE('" + TextUtility.formatDate(TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getSecToTime()),"yyyy-MM-dd HH:mm:ss") + "','YYYY-MM-DD HH24:MI:SS') ");
                } else {
                  sqlStat.append("AND " + "A.SENT_DATE" + " " + "<" + " '" + TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getSecToTime()) + "' ");
                }
              }
              sqlStat.append(")"+" ");
            }
            if(Utility.isEmpty(emailAuditTrail.getSecToTime())){
              if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
                sqlStat.append("OR " + "A.SENT_DATE" + " " + "<" + " TO_DATE('" + TextUtility.formatDate(TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getSecFromTime()),"yyyy-MM-dd HH:mm:ss") + "','YYYY-MM-DD HH24:MI:SS') ");
              } else {
                sqlStat.append("OR " + "A.SENT_DATE" + " " + "<" + " '" + TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getSecFromTime()) + "' ");
              }
            }
          }
          
        }
        if(!Utility.isEmpty(emailAuditTrail.getAttchName())){
          sqlStat.append("AND " + "A.ATTCH_NAME" + " " + emailAuditTrail.getAttchNameSearchType() + " ? ");
        }
        if(!Utility.isEmpty(emailAuditTrail.getEmailType())){
          sqlStat.append("AND " + "A.EMAIL_TYPE" + " " + emailAuditTrail.getTypeSearchType() + " ? ");
        }
        
        //format the sql for any 'LIKE' statement contained
        sqlStat = this.getFormattedSQL(sqlStat.toString());
        
        //Form the ORDER clause for sorting.
        if (!Utility.isEmpty(emailAuditTrail.getSortAttribute())) {
          String sortAttribute = emailAuditTrail.getSortAttribute();
          if (sortAttribute.indexOf(".") < 0) {
            sortAttribute = "A." + sortAttribute;
          }
          sqlStat.append("ORDER BY " + sortAttribute + " " + emailAuditTrail.getSortOrder());
        }
        
        // Get total number of record return.
        sqlStatCnt = this.getSelectCountSQL(sqlStat);
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        
        // Retrieve the result in row basis.
        sqlStat = this.getSelectListSQL(sqlStat, startOffset, pageSize);
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        
        int i=0;
        //this.setPrepareStatement(preStatCnt, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        if (!Utility.isEmpty(emailAuditTrail.getOperation())){
          i++;
          this.setPrepareStatement(preStatCnt, i, emailAuditTrail.getOperation());
          this.setPrepareStatement(preStat, i, emailAuditTrail.getOperation());
        }
        if(!Utility.isEmpty(emailAuditTrail.getUserid())){
          i++;
          if(emailAuditTrail.getUserSearchType().equals("LIKE")){
            this.setPrepareStatement(preStatCnt, i, "%"+emailAuditTrail.getUserid()+"%");
            this.setPrepareStatement(preStat, i, "%"+emailAuditTrail.getUserid()+"%");
          }else{
            this.setPrepareStatement(preStatCnt, i, emailAuditTrail.getUserid());
            this.setPrepareStatement(preStat, i, emailAuditTrail.getUserid());
          }
        }
        if(!Utility.isEmpty(emailAuditTrail.getEmailTo())){
          i++;
          if(emailAuditTrail.getToSearchType().equals("LIKE")){
            this.setPrepareStatement(preStatCnt, i, "%"+emailAuditTrail.getEmailTo()+"%");
            this.setPrepareStatement(preStat, i, "%"+emailAuditTrail.getEmailTo()+"%");
          }else{
            this.setPrepareStatement(preStatCnt, i, emailAuditTrail.getEmailTo());
            this.setPrepareStatement(preStat, i, emailAuditTrail.getEmailTo());
          }
        }
        if(!Utility.isEmpty(emailAuditTrail.getEmailFrom())){
          i++;
          if(emailAuditTrail.getFromSearchType().equals("LIKE")){
            this.setPrepareStatement(preStatCnt, i, "%"+emailAuditTrail.getEmailFrom()+"%");
            this.setPrepareStatement(preStat, i, "%"+emailAuditTrail.getEmailFrom()+"%");
          }else{
            this.setPrepareStatement(preStatCnt, i, emailAuditTrail.getEmailFrom());
            this.setPrepareStatement(preStat, i, emailAuditTrail.getEmailFrom());
          }
        }
        if(!Utility.isEmpty(emailAuditTrail.getEmailCc())){
          i++;
          if(emailAuditTrail.getCcSearchType().equals("LIKE")){
            this.setPrepareStatement(preStatCnt, i, "%"+emailAuditTrail.getEmailCc()+"%");
            this.setPrepareStatement(preStat, i, "%"+emailAuditTrail.getEmailCc()+"%");
          }else{
            this.setPrepareStatement(preStatCnt, i, emailAuditTrail.getEmailCc());
            this.setPrepareStatement(preStat, i, emailAuditTrail.getEmailCc());
          }
        }
        if(!Utility.isEmpty(emailAuditTrail.getEmailBcc())){
          i++;
          if(emailAuditTrail.getBccSearchType().equals("LIKE")){
            this.setPrepareStatement(preStatCnt, i, "%"+emailAuditTrail.getEmailBcc()+"%");
            this.setPrepareStatement(preStat, i, "%"+emailAuditTrail.getEmailBcc()+"%");
          }else{
            this.setPrepareStatement(preStatCnt, i, emailAuditTrail.getEmailBcc());
            this.setPrepareStatement(preStat, i, emailAuditTrail.getEmailBcc());
          }	
        }
        if(!Utility.isEmpty(emailAuditTrail.getEmailSubject())){
          i++;
          if(emailAuditTrail.getSubjectSearchType().equals("LIKE")){
            this.setPrepareStatement(preStatCnt, i, "%"+emailAuditTrail.getEmailSubject()+"%");
            this.setPrepareStatement(preStat, i, "%"+emailAuditTrail.getEmailSubject()+"%");
          }else{
            this.setPrepareStatement(preStatCnt, i, emailAuditTrail.getEmailSubject());
            this.setPrepareStatement(preStat, i, emailAuditTrail.getEmailSubject());	
          }
          
        }
        
        /*
        if(!Utility.isEmpty(emailAuditTrail.getSentDate())&& emailAuditTrail.getSentDateValue().equals("specificDate")){
          if(!Utility.isEmpty(emailAuditTrail.getSentDate())){
            i++;
            //this.setPrepareStatement(preStatCnt, i, emailAuditTrail.getSentDate());
            this.setPrepareStatement(preStatCnt, i, TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getSentDate()));
            //this.setPrepareStatement(preStat, i, emailAuditTrail.getSentDate());
            this.setPrepareStatement(preStat, i, TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getSentDate()));
          }
          if(!Utility.isEmpty(emailAuditTrail.getSecondDate())){
            i++;
            //this.setPrepareStatement(preStatCnt, i, emailAuditTrail.getSecondDate());
            this.setPrepareStatement(preStatCnt, i, TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getSecondDate()));
            //this.setPrepareStatement(preStat, i, emailAuditTrail.getSecondDate());
            this.setPrepareStatement(preStat, i, TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getSecondDate()));
          }
        }else if(!Utility.isEmpty(emailAuditTrail.getSentDateValue())&&!Utility.isEmpty(emailAuditTrail.getFromTime())){      	
          if(!Utility.isEmpty(emailAuditTrail.getToTime())){
            i++;
            //this.setPrepareStatement(preStatCnt, i, emailAuditTrail.getFromTime());
            this.setPrepareStatement(preStatCnt, i, TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getFromTime()));
            //this.setPrepareStatement(preStat, i, emailAuditTrail.getFromTime());
            this.setPrepareStatement(preStat, i, TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getFromTime()));
            if(true){
              i++;
              //this.setPrepareStatement(preStatCnt, i, emailAuditTrail.getToTime());
              this.setPrepareStatement(preStatCnt, i, TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getToTime()));
              //this.setPrepareStatement(preStat, i, emailAuditTrail.getToTime());
              this.setPrepareStatement(preStat, i, TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getToTime()));
            }
          }
          if(Utility.isEmpty(emailAuditTrail.getToTime())){
            i++;
            //this.setPrepareStatement(preStatCnt, i, emailAuditTrail.getFromTime());
            this.setPrepareStatement(preStatCnt, i, TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getFromTime()));
            //this.setPrepareStatement(preStat, i, emailAuditTrail.getFromTime());
            this.setPrepareStatement(preStat, i, TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getFromTime()));
          }
          if(Utility.isEmpty(emailAuditTrail.getSecondDate())&&!Utility.isEmpty(emailAuditTrail.getSecFromTime())){
            
            if(!Utility.isEmpty(emailAuditTrail.getSecToTime())){
              i++;
              //this.setPrepareStatement(preStatCnt, i, emailAuditTrail.getSecFromTime());
              this.setPrepareStatement(preStatCnt, i, TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getSecFromTime()));
              //this.setPrepareStatement(preStat, i, emailAuditTrail.getSecFromTime());
              this.setPrepareStatement(preStat, i, TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getSecFromTime()));
              if(true){
                i++;
                //this.setPrepareStatement(preStatCnt, i, emailAuditTrail.getSecToTime());
                this.setPrepareStatement(preStatCnt, i, TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getSecToTime()));
                //this.setPrepareStatement(preStat, i, emailAuditTrail.getSecToTime());
                this.setPrepareStatement(preStat, i, TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getSecToTime()));
              }
            }
            if(Utility.isEmpty(emailAuditTrail.getSecToTime())){
              i++;
              //this.setPrepareStatement(preStatCnt, i, emailAuditTrail.getSecFromTime());
              this.setPrepareStatement(preStatCnt, i, TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getSecFromTime()));
              //this.setPrepareStatement(preStat, i, emailAuditTrail.getSecFromTime());
              this.setPrepareStatement(preStat, i, TextUtility.parseDBDateTimeToTimeStamp(emailAuditTrail.getSecFromTime()));
            }
          }
          
        }
        */
        
        if(!Utility.isEmpty(emailAuditTrail.getAttchName())){
          i++;
          if(emailAuditTrail.getAttchNameSearchType().equals("LIKE")){
            this.setPrepareStatement(preStatCnt, i, "%"+emailAuditTrail.getAttchName()+"%");
            this.setPrepareStatement(preStat, i, "%"+emailAuditTrail.getAttchName()+"%");
          }else{
            this.setPrepareStatement(preStatCnt, i, emailAuditTrail.getAttchName());
            this.setPrepareStatement(preStat, i, emailAuditTrail.getAttchName());	
          }
        }
        if(!Utility.isEmpty(emailAuditTrail.getEmailType())){
          i++;
          this.setPrepareStatement(preStatCnt, i, emailAuditTrail.getEmailType());
          this.setPrepareStatement(preStat, i, emailAuditTrail.getEmailType());
        }
        rsCnt = preStatCnt.executeQuery();
        if (rsCnt.next()) {
          totalNumOfRecord = rsCnt.getInt(1);
        }
        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
        
        rs = preStat.executeQuery();
        
        this.positionCursor(rs, startOffset, pageSize);
        
        while (rs.next() && rowLoopCnt<pageSize) {
          DmsEmailAuditTrail tmpDmsEmailAuditTrail = new DmsEmailAuditTrail();
          tmpDmsEmailAuditTrail.setID(getResultSetInteger(rs, "ID"));
          tmpDmsEmailAuditTrail.setEmailType(getResultSetString(rs, "EMAIL_TYPE"));
          tmpDmsEmailAuditTrail.setOperation(getResultSetString(rs, "OPERATION"));
          tmpDmsEmailAuditTrail.setUserid(getResultSetInteger(rs, "USER_ID"));
          tmpDmsEmailAuditTrail.setEmailSubject(getResultSetString(rs, "EMAIL_SUBJECT"));
          tmpDmsEmailAuditTrail.setEmailFrom(getResultSetString(rs, "EMAIL_FROM"));
          tmpDmsEmailAuditTrail.setEmailCc(getResultSetString(rs, "EMAIL_CC"));
          tmpDmsEmailAuditTrail.setEmailBcc(getResultSetString(rs, "EMAIL_BCC"));
          tmpDmsEmailAuditTrail.setEmailTo(getResultSetString(rs, "EMAIL_TO"));
          tmpDmsEmailAuditTrail.setSentDate(getResultSetString(rs, "SENT_DATE"));
          tmpDmsEmailAuditTrail.setAttchName(getResultSetString(rs, "ATTCH_NAME"));
          tmpDmsEmailAuditTrail.setAttchSource(getResultSetString(rs, "ATTCH_SOURCE"));
          tmpDmsEmailAuditTrail.setUserName(UserInfoFactory.getUserFullName(tmpDmsEmailAuditTrail.getUserid()));
          tmpDmsEmailAuditTrail.setAttchCount(getResultSetInteger(rs,"ATTCH_COUNT"));
          tmpDmsEmailAuditTrail.setRecordCount(totalNumOfRecord);
          tmpDmsEmailAuditTrail.setRowNum(startOffset++);
          ++rowLoopCnt;
          result.add(tmpDmsEmailAuditTrail);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
      }
    }
  }
  
  protected synchronized List getList(AbstractSearchForm searchForm) throws ApplicationException {
    PreparedStatement preStat = null;
    PreparedStatement preStatCnt = null;
    ResultSet rs = null;
    ResultSet rsCnt = null;
    StringBuffer sqlStat = new StringBuffer();
    StringBuffer sqlStatCnt = new StringBuffer();
    List result = new ArrayList();
    int totalNumOfRecord = 0;
    int rowLoopCnt = 0;
    int startOffset = TextUtility.parseInteger(searchForm.getCurStartRowNo());
    int pageSize = TextUtility.parseInteger(searchForm.getPageOffset());
    
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.EMAIL_TYPE, A.OPERATION, A.IP, A.USERID, A.EMAIL_SUBJECT, A.EMAIL_FROM, A.EMAIL_CC, A.EMAIL_BCC, A.EMAIL_TO, A.SENT_DATE, A.ATTCH_NAME, A.ATTCH_SOURCE ");
        sqlStat.append("FROM   DMS_EMAIL_AUDIT_TRAIL A ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? ");
        
        // Form the WHERE clause for filtering.
        if (searchForm.isSearchable()) {
          String searchField = getSearchColumn(searchForm.getBasicSearchField());
          sqlStat.append("AND  " + searchField + " " + searchForm.getBasicSearchType() + " ? ");
        }
        
        //format the sql for any 'LIKE' statement contained
        sqlStat = this.getFormattedSQL(sqlStat.toString());
        
        // Form the ORDER clause for sorting.
        if (searchForm.isSortable()) {
          String sortAttribute = searchForm.getSortAttribute();
          if (sortAttribute.indexOf(".") < 0) {
            sortAttribute = "A." + sortAttribute;
          }
          sqlStat.append("ORDER BY " + sortAttribute + " " + searchForm.getSortOrder());
        }
        
        // Get total number of record return.
        sqlStatCnt = this.getSelectCountSQL(sqlStat);
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStatCnt, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        if (searchForm.isSearchable()) {
          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(), searchForm.getBasicSearchType());
          this.setPrepareStatement(preStatCnt, 2, searchKeyword);
        }
        rsCnt = preStatCnt.executeQuery();
        if (rsCnt.next()) {
          totalNumOfRecord = rsCnt.getInt(1);
        }
        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
        
        // Retrieve the result in row basis.
        sqlStat = this.getSelectListSQL(sqlStat, startOffset, pageSize);
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        if (searchForm.isSearchable()) {
          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(), searchForm.getBasicSearchType());
          this.setPrepareStatement(preStat, 2, searchKeyword);
        }
        rs = preStat.executeQuery();
        
        this.positionCursor(rs, startOffset, pageSize);
        
        while (rs.next() && rowLoopCnt<pageSize) {
          DmsEmailAuditTrail tmpDmsEmailAuditTrail = new DmsEmailAuditTrail();
          tmpDmsEmailAuditTrail.setID(getResultSetInteger(rs, "ID"));
          tmpDmsEmailAuditTrail.setEmailType(getResultSetString(rs, "EMAIL_TYPE"));
          tmpDmsEmailAuditTrail.setOperation(getResultSetString(rs, "OPERATION"));
          tmpDmsEmailAuditTrail.setUserid(getResultSetInteger(rs, "USERID"));
          tmpDmsEmailAuditTrail.setEmailSubject(getResultSetString(rs, "EMAIL_SUBJECT"));
          tmpDmsEmailAuditTrail.setEmailFrom(getResultSetString(rs, "EMAIL_FROM"));
          tmpDmsEmailAuditTrail.setEmailCc(getResultSetString(rs, "EMAIL_CC"));
          tmpDmsEmailAuditTrail.setEmailBcc(getResultSetString(rs, "EMAIL_BCC"));
          tmpDmsEmailAuditTrail.setEmailTo(getResultSetString(rs, "EMAIL_TO"));
          tmpDmsEmailAuditTrail.setSentDate(getResultSetTimestamp(rs, "SENT_DATE").toString());
          tmpDmsEmailAuditTrail.setAttchName(getResultSetString(rs, "ATTCH_NAME"));
          tmpDmsEmailAuditTrail.setAttchSource(getResultSetString(rs, "ATTCH_SOURCE"));
          tmpDmsEmailAuditTrail.setCreatorName(UserInfoFactory.getUserFullName(tmpDmsEmailAuditTrail.getCreatorID()));
          tmpDmsEmailAuditTrail.setUpdaterName(UserInfoFactory.getUserFullName(tmpDmsEmailAuditTrail.getUpdaterID()));
          tmpDmsEmailAuditTrail.setRecordCount(totalNumOfRecord);
          tmpDmsEmailAuditTrail.setRowNum(startOffset++);
          ++rowLoopCnt;
          result.add(tmpDmsEmailAuditTrail);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
      }
    }
  }
  
  protected synchronized List getList() throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();
    
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.EMAIL_TYPE, A.OPERATION, A.IP, A.USERID, A.EMAIL_SUBJECT, A.EMAIL_FROM, A.EMAIL_CC, A.EMAIL_BCC, A.EMAIL_TO, A.SENT_DATE, A.ATTCH_NAME, A.ATTCH_SOURCE ");
        sqlStat.append("FROM   DMS_EMAIL_AUDIT_TRAIL A ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();
        while (rs.next()) {
          DmsEmailAuditTrail tmpDmsEmailAuditTrail = new DmsEmailAuditTrail();
          tmpDmsEmailAuditTrail.setID(getResultSetInteger(rs, "ID"));
          tmpDmsEmailAuditTrail.setEmailType(getResultSetString(rs, "EMAIL_TYPE"));
          tmpDmsEmailAuditTrail.setOperation(getResultSetString(rs, "OPERATION"));
          tmpDmsEmailAuditTrail.setUserid(getResultSetInteger(rs, "USERID"));
          tmpDmsEmailAuditTrail.setEmailSubject(getResultSetString(rs, "EMAIL_SUBJECT"));
          tmpDmsEmailAuditTrail.setEmailFrom(getResultSetString(rs, "EMAIL_FROM"));
          tmpDmsEmailAuditTrail.setEmailCc(getResultSetString(rs, "EMAIL_CC"));
          tmpDmsEmailAuditTrail.setEmailBcc(getResultSetString(rs, "EMAIL_BCC"));
          tmpDmsEmailAuditTrail.setEmailTo(getResultSetString(rs, "EMAIL_TO"));
          tmpDmsEmailAuditTrail.setSentDate(getResultSetTimestamp(rs, "SENT_DATE").toString());
          tmpDmsEmailAuditTrail.setAttchName(getResultSetString(rs, "ATTCH_NAME"));
          tmpDmsEmailAuditTrail.setAttchSource(getResultSetString(rs, "ATTCH_SOURCE"));
          tmpDmsEmailAuditTrail.setCreatorName(UserInfoFactory.getUserFullName(tmpDmsEmailAuditTrail.getCreatorID()));
          tmpDmsEmailAuditTrail.setUpdaterName(UserInfoFactory.getUserFullName(tmpDmsEmailAuditTrail.getUpdaterID()));
          result.add(tmpDmsEmailAuditTrail);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  protected void validateInsert(AbstractBaseObject obj) throws ApplicationException {
  }
  
  protected void validateUpdate(AbstractBaseObject obj) throws ApplicationException {
  }
  
  protected void validateDelete(AbstractBaseObject obj) throws ApplicationException {
  }
  
  protected synchronized AbstractBaseObject insert(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    DmsEmailAuditTrail tmpDmsEmailAuditTrail = (DmsEmailAuditTrail)((DmsEmailAuditTrail)obj).clone();
    
    synchronized(dbConn) {
      try {
        Integer nextID = getNextPrimaryID();
        sqlStat.append("INSERT ");
        sqlStat.append("INTO   DMS_EMAIL_AUDIT_TRAIL(ID, EMAIL_TYPE, OPERATION,  USER_ID, EMAIL_SUBJECT, EMAIL_FROM, EMAIL_CC, EMAIL_BCC, EMAIL_TO, SENT_DATE, ATTCH_NAME, ATTCH_SOURCE,ATTCH_COUNT,RECORD_STATUS,CREATOR_ID,CREATE_DATE,UPDATE_COUNT,UPDATER_ID,UPDATE_DATE) ");
        sqlStat.append("VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?,?,?,?,?) ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, nextID);
        setPrepareStatement(preStat, 2, tmpDmsEmailAuditTrail.getEmailType());
        setPrepareStatement(preStat, 3, tmpDmsEmailAuditTrail.getOperation());
        setPrepareStatement(preStat, 4, tmpDmsEmailAuditTrail.getUserid());
        setPrepareStatement(preStat, 5, tmpDmsEmailAuditTrail.getEmailSubject());
        setPrepareStatement(preStat, 6, tmpDmsEmailAuditTrail.getEmailFrom());
        setPrepareStatement(preStat, 7, tmpDmsEmailAuditTrail.getEmailCc());
        setPrepareStatement(preStat, 8, tmpDmsEmailAuditTrail.getEmailBcc());
        setPrepareStatement(preStat, 9, tmpDmsEmailAuditTrail.getEmailTo());
        setPrepareStatement(preStat, 10, TextUtility.parseDBDateTimeToTimeStamp(tmpDmsEmailAuditTrail.getSentDate()));//Update By Jim Zhou      2007/02/01      EIP-1694
        setPrepareStatement(preStat, 11, tmpDmsEmailAuditTrail.getAttchName());
        setPrepareStatement(preStat, 12, tmpDmsEmailAuditTrail.getAttchSource());
        setPrepareStatement(preStat, 13, tmpDmsEmailAuditTrail.getAttchCount());
        setPrepareStatement(preStat, 14, tmpDmsEmailAuditTrail.getRecordStatus());
        setPrepareStatement(preStat, 15, tmpDmsEmailAuditTrail.getCreatorID());
        setPrepareStatement(preStat, 16, tmpDmsEmailAuditTrail.getCreateDate());
        setPrepareStatement(preStat, 17, tmpDmsEmailAuditTrail.getUpdateCount());
        setPrepareStatement(preStat, 18, tmpDmsEmailAuditTrail.getUpdaterID());
        setPrepareStatement(preStat, 19, tmpDmsEmailAuditTrail.getUpdateDate());
        preStat.executeUpdate();
        tmpDmsEmailAuditTrail.setID(nextID);
        tmpDmsEmailAuditTrail.setUserName(UserInfoFactory.getUserFullName(tmpDmsEmailAuditTrail.getUserid()));
        return(tmpDmsEmailAuditTrail);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_INSERT_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  protected synchronized AbstractBaseObject update(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    DmsEmailAuditTrail tmpDmsEmailAuditTrail = (DmsEmailAuditTrail)((DmsEmailAuditTrail)obj).clone();
    
    synchronized(dbConn) {
      try {
        int updateCnt = 0;
        Timestamp currTime = Utility.getCurrentTimestamp();
        sqlStat.append("UPDATE DMS_EMAIL_AUDIT_TRAIL ");
        sqlStat.append("SET  EMAIL_TYPE=?, OPERATION=?, USERID=?, EMAIL_SUBJECT=?, EMAIL_FROM=?, EMAIL_CC=?, EMAIL_BCC=?, EMAIL_TO=?, SENT_DATE=?, ATTCH_NAME=?, ATTCH_SOURCE=? ");
        sqlStat.append("WHERE  ID=? AND UPDATE_COUNT=? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, tmpDmsEmailAuditTrail.getEmailType());
        setPrepareStatement(preStat, 2, tmpDmsEmailAuditTrail.getOperation());
        setPrepareStatement(preStat, 4, tmpDmsEmailAuditTrail.getUserid());
        setPrepareStatement(preStat, 5, tmpDmsEmailAuditTrail.getEmailSubject());
        setPrepareStatement(preStat, 6, tmpDmsEmailAuditTrail.getEmailFrom());
        setPrepareStatement(preStat, 7, tmpDmsEmailAuditTrail.getEmailCc());
        setPrepareStatement(preStat, 8, tmpDmsEmailAuditTrail.getEmailBcc());
        setPrepareStatement(preStat, 9, tmpDmsEmailAuditTrail.getEmailTo());
        setPrepareStatement(preStat, 10, tmpDmsEmailAuditTrail.getSentDate());
        setPrepareStatement(preStat, 11, tmpDmsEmailAuditTrail.getAttchName());
        setPrepareStatement(preStat, 12, tmpDmsEmailAuditTrail.getAttchSource());
        setPrepareStatement(preStat, 13, new Integer(tmpDmsEmailAuditTrail.getUpdateCount().intValue() + 1));
        setPrepareStatement(preStat, 14, sessionContainer.getUserRecordID());
        setPrepareStatement(preStat, 15, currTime);
        setPrepareStatement(preStat, 16, tmpDmsEmailAuditTrail.getID());
        setPrepareStatement(preStat, 17, tmpDmsEmailAuditTrail.getUpdateCount());
        updateCnt = preStat.executeUpdate();
        if (updateCnt == 0) {
          throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);
        } else {
          tmpDmsEmailAuditTrail.setUpdaterID(sessionContainer.getUserRecordID());
          tmpDmsEmailAuditTrail.setUpdateDate(currTime);
          tmpDmsEmailAuditTrail.setUpdateCount(new Integer(tmpDmsEmailAuditTrail.getUpdateCount().intValue() + 1));
          tmpDmsEmailAuditTrail.setCreatorName(UserInfoFactory.getUserFullName(tmpDmsEmailAuditTrail.getCreatorID()));
          tmpDmsEmailAuditTrail.setUpdaterName(UserInfoFactory.getUserFullName(tmpDmsEmailAuditTrail.getUpdaterID()));
          return(tmpDmsEmailAuditTrail);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_UPDATE_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  protected synchronized AbstractBaseObject delete(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    DmsEmailAuditTrail tmpDmsEmailAuditTrail = (DmsEmailAuditTrail)((DmsEmailAuditTrail)obj).clone();
    
    synchronized(dbConn) {
      try {
        int updateCnt = 0;
        sqlStat.append("DELETE ");
        sqlStat.append("FROM   DMS_EMAIL_AUDIT_TRAIL ");
        sqlStat.append("WHERE  ID=? AND UPDATE_COUNT=? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, tmpDmsEmailAuditTrail.getID());
        setPrepareStatement(preStat, 2, tmpDmsEmailAuditTrail.getUpdateCount());
        updateCnt = preStat.executeUpdate();
        if (updateCnt == 0) {
          throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);
        } else {
          return(tmpDmsEmailAuditTrail);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_DELETE_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  protected synchronized void auditTrail(String opMode, AbstractBaseObject obj) throws ApplicationException {
    Vector oldValues = new Vector();
    Vector newValues = new Vector();
    DmsEmailAuditTrail tmpDmsEmailAuditTrail = (DmsEmailAuditTrail)this.oldValue;
    
    if (tmpDmsEmailAuditTrail != null) {
      oldValues.add(toAuditTrailValue(tmpDmsEmailAuditTrail.getID()));
      oldValues.add(toAuditTrailValue(tmpDmsEmailAuditTrail.getEmailType()));
      oldValues.add(toAuditTrailValue(tmpDmsEmailAuditTrail.getOperation()));
      oldValues.add(toAuditTrailValue(tmpDmsEmailAuditTrail.getUserid()));
      oldValues.add(toAuditTrailValue(tmpDmsEmailAuditTrail.getEmailSubject()));
      oldValues.add(toAuditTrailValue(tmpDmsEmailAuditTrail.getEmailFrom()));
      oldValues.add(toAuditTrailValue(tmpDmsEmailAuditTrail.getEmailCc()));
      oldValues.add(toAuditTrailValue(tmpDmsEmailAuditTrail.getEmailBcc()));
      oldValues.add(toAuditTrailValue(tmpDmsEmailAuditTrail.getEmailTo()));
      oldValues.add(toAuditTrailValue(tmpDmsEmailAuditTrail.getSentDate()));
      oldValues.add(toAuditTrailValue(tmpDmsEmailAuditTrail.getAttchName()));
      oldValues.add(toAuditTrailValue(tmpDmsEmailAuditTrail.getAttchSource()));
    }
    
    tmpDmsEmailAuditTrail = (DmsEmailAuditTrail)obj;
    if (tmpDmsEmailAuditTrail != null) {
      newValues.add(toAuditTrailValue(tmpDmsEmailAuditTrail.getID()));
      newValues.add(toAuditTrailValue(tmpDmsEmailAuditTrail.getEmailType()));
      newValues.add(toAuditTrailValue(tmpDmsEmailAuditTrail.getOperation()));
      newValues.add(toAuditTrailValue(tmpDmsEmailAuditTrail.getUserid()));
      newValues.add(toAuditTrailValue(tmpDmsEmailAuditTrail.getEmailSubject()));
      newValues.add(toAuditTrailValue(tmpDmsEmailAuditTrail.getEmailFrom()));
      newValues.add(toAuditTrailValue(tmpDmsEmailAuditTrail.getEmailCc()));
      newValues.add(toAuditTrailValue(tmpDmsEmailAuditTrail.getEmailBcc()));
      newValues.add(toAuditTrailValue(tmpDmsEmailAuditTrail.getEmailTo()));
      newValues.add(toAuditTrailValue(tmpDmsEmailAuditTrail.getSentDate()));
      newValues.add(toAuditTrailValue(tmpDmsEmailAuditTrail.getAttchName()));
      newValues.add(toAuditTrailValue(tmpDmsEmailAuditTrail.getAttchSource()));
    }
    
    auditTrailBase(opMode, oldValues, newValues);
  }
  
  /***********************************************************************
   * DON'T Modify the codes above unless you know what you are doing!!!  *
   * Put your own functions beblow.                                      *
   * For FINDER methods, the function name should be in the notation:    *
   *   public Object getObjectBy<Criteria>()                             *
   *   - e.g. public Object getObjectByCode()                            *
   *   public List getListBy<Criteria>()                                 *
   *   - e.g. public List getListByUserID()                              *
   * For OPERATION methods, the function name should be in the notation: *
   *   public void <Operation>ObjectBy<Criteria>()                       *
   *   - e.g. public void deleteObjectByCode()                           *
   *   public void <Operation>ListBy<Criteria>()                         *
   *   - e.g. public void deleteListByUserID()                           *
   ***********************************************************************/
  
}
